using System;
using System.Data;
using System.Data.SqlClient;


/// <summary>
/// Summary description for NewEditJSUserClass
/// </summary>
public partial class SqlServerProvider
{

    public bool AddNewUpdateCoverLetter(
    string letterID,
    string userID,
    string subject,
    string body)
    {
        errorMessage = "";
        bool save = false;
        try
        {
            
            sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SPJSCoverLetter";
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType =
            CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue(
            "@LetterID", letterID);
            sqlCommand.Parameters.AddWithValue(
            "@UserID", userID);
            sqlCommand.Parameters.AddWithValue(
            "@Subject", subject);
            sqlCommand.Parameters.AddWithValue(
            "@Body", body);            
            sqlCommand.ExecuteNonQuery();
            save = true;
            CloseConnection();
        }
        catch (SqlException sql)
        {
            errorCode = sql.Number;
            errorMessage = sql.Message;
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;

        }
        return save;
    }
    
    public string NewJSAccount( 
    string email, string password,
    string firstName, 
    string lastName, string newsletter ,
	string jobAlert , string companyInfo)
    {
        errorMessage = "";
        string  userID = "";
        try
        {
            
            sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SPJSAccount";
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType =
            CommandType.StoredProcedure;
             
            sqlCommand.Parameters.AddWithValue(
            "@UserID", "0");
            sqlCommand.Parameters.AddWithValue(
            "@Email", email);
            Common common = new Common();
            sqlCommand.Parameters.AddWithValue(
            "@Password", common.RCVEncrypting(password,"rcv"));
            sqlCommand.Parameters.AddWithValue(
            "@FirstName", firstName);
            sqlCommand.Parameters.AddWithValue(
            "@LastName", lastName);
            sqlCommand.Parameters.AddWithValue(
            "@Newsletter", newsletter);
            sqlCommand.Parameters.AddWithValue(
            "@JobAlert", jobAlert);
            sqlCommand.Parameters.AddWithValue(
            "@CompanyInfo", companyInfo);
             
            SqlParameter sqlParameter=
            new SqlParameter("@ReturnValue", "");
            sqlParameter.Direction = ParameterDirection.ReturnValue;
            sqlCommand.Parameters.Add(sqlParameter);
           
            sqlCommand.ExecuteNonQuery();
            userID = Convert.ToString(sqlParameter.Value);
             
            CloseConnection();
        }
        catch(SqlException sql)
        {
            errorCode=sql.Number;
            errorMessage = sql.Message;
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;
            
        }
        return userID;
    }
    public bool UpdateJSAccount(string userID,
        string email,  
        string firstName,
        string lastName, string newsletter,
        string jobAlert, string companyInfo)
    {
        errorMessage = "";
        bool update = false;
        try
        {
             
            sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SPJSAccount";
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType =
            CommandType.StoredProcedure;

            sqlCommand.Parameters.AddWithValue(
            "@UserID", userID);
            sqlCommand.Parameters.AddWithValue(
            "@Email", email);           
            sqlCommand.Parameters.AddWithValue(
            "@Password", "");
            sqlCommand.Parameters.AddWithValue(
            "@FirstName", firstName);
            sqlCommand.Parameters.AddWithValue(
            "@LastName", lastName);
            sqlCommand.Parameters.AddWithValue(
            "@Newsletter", newsletter);
            sqlCommand.Parameters.AddWithValue(
            "@JobAlert", jobAlert);
            sqlCommand.Parameters.AddWithValue(
            "@CompanyInfo", companyInfo);
             
            sqlCommand.ExecuteNonQuery();
            update=true; 
            CloseConnection();
        }
        catch (SqlException sql)
        {
            errorCode = sql.Number;
            errorMessage = sql.Message;
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;

        }
        return update;
    }
    public bool UpdatePersonal(
    string userID,
    string permanent,
    string resident,
    short provinceID,
    string city, 
    string dob,
    bool gender, 
    short maritalStatus,
    short raceID)
    {
        errorMessage = "";
        bool update =false;
        try
        {
            
            sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SPJSPersonal";
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType =
            CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue(
            "@UserID", userID);
            sqlCommand.Parameters.AddWithValue(
            "@Address", permanent);            
            sqlCommand.Parameters.AddWithValue(
            "@Resident", resident);
            sqlCommand.Parameters.AddWithValue(
            "@ProvinceID", provinceID);
            sqlCommand.Parameters.AddWithValue(
            "@City", city);
            sqlCommand.Parameters.AddWithValue(
            "@DOB", dob);
            sqlCommand.Parameters.AddWithValue(
            "@Gender", gender);
            sqlCommand.Parameters.AddWithValue(
            "@MaritalStatus", maritalStatus);
            sqlCommand.Parameters.AddWithValue(
            "@RaceID", raceID);

            sqlCommand.ExecuteNonQuery();
            update=true;
            CloseConnection();
        }
        catch (SqlException sql)
        {
            errorCode = sql.Number;
            errorMessage = sql.Message;
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;

        }
        return update;
    }

    public bool AddNewUpdateEducation(
    string educationID, 
    string userID,
    string school,
    string country,
    string faculty,
    string major, 
    string certificate,
    string mode,
    string from, string to,
    string description)
    {
        errorMessage = "";
        bool save = false;
        try
        {
            
            sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SPJSEducation";
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType =
            CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue(
            "@EducationID", educationID);
            sqlCommand.Parameters.AddWithValue(
            "@UserID", userID);
            sqlCommand.Parameters.AddWithValue(
            "@School", school);
            sqlCommand.Parameters.AddWithValue(
            "@Country", country);
            sqlCommand.Parameters.AddWithValue(
            "@Faculty", faculty);
            sqlCommand.Parameters.AddWithValue(
            "@Major", major);
            sqlCommand.Parameters.AddWithValue(
            "@Certificate", certificate);
            sqlCommand.Parameters.AddWithValue(
            "@Mode", mode);
            sqlCommand.Parameters.AddWithValue(
            "@FromMonthYear", from);
            sqlCommand.Parameters.AddWithValue(
            "@ToMonthYear", to);
            sqlCommand.Parameters.AddWithValue(
            "@Description", description);

            sqlCommand.ExecuteNonQuery();
            save = true;
            CloseConnection();
        }
        catch (SqlException sql)
        {
            errorCode = sql.Number;
            errorMessage = sql.Message;
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;

        }
        return save;
    }

    public bool AddNewUpdateExperience(
    string experienceID,
    string userID,
    string company,
    string address,
    string country,
    string category,
    string summary,
    string position, string salary,
    string from, string to,
    string description)
    {
        errorMessage = "";
        bool save = false;
        try
        {
            
            sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SPJSExperience";
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType =
            CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue(
            "@ExperienceID", experienceID);
            sqlCommand.Parameters.AddWithValue(
            "@UserID", userID);
            sqlCommand.Parameters.AddWithValue(
            "@Company", company);
            sqlCommand.Parameters.AddWithValue(
            "@Address", address);
            sqlCommand.Parameters.AddWithValue(
            "@Country", country);
            sqlCommand.Parameters.AddWithValue(
            "@JobCategoryID", category);
            sqlCommand.Parameters.AddWithValue(
            "@JobSummary", summary);
            sqlCommand.Parameters.AddWithValue(
            "@JobPosition", position);
            sqlCommand.Parameters.AddWithValue(
            "@Salary", salary);
            sqlCommand.Parameters.AddWithValue(
            "@FromMonthYear", from);
            sqlCommand.Parameters.AddWithValue(
            "@ToMonthYear", to);
            sqlCommand.Parameters.AddWithValue(
            "@Description", description);

            sqlCommand.ExecuteNonQuery();
            save = true;
            CloseConnection();
        }
        catch (SqlException sql)
        {
            errorCode = sql.Number;
            errorMessage = sql.Message;
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;

        }
        return save;
    }
    
    public bool AddNewUpdateSkill(
    string skillID,
    string userID,
    string speed,
    string software,
    string vnspeaking,
    string vnreading,
    string vnlistening,
    string enspeaking,
    string enreading,
    string enlistening,
    string description)
    {
        errorMessage = "";
        bool save = false;
        try
        {
            
            sqlCommand = new SqlCommand();
            sqlCommand.CommandText = "SPJSSkill";
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType =
            CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue(
            "@SkillID", skillID);
            sqlCommand.Parameters.AddWithValue(
            "@UserID", userID);
            sqlCommand.Parameters.AddWithValue(
            "@Speed", speed);
            sqlCommand.Parameters.AddWithValue(
            "@Software", software);
            sqlCommand.Parameters.AddWithValue(
            "@VNSpeaking", vnspeaking);
            sqlCommand.Parameters.AddWithValue(
            "@VNReading", vnreading);
            sqlCommand.Parameters.AddWithValue(
            "@VNListening", vnlistening);
            sqlCommand.Parameters.AddWithValue(
            "@ENSpeaking", enspeaking);
            sqlCommand.Parameters.AddWithValue(
            "@ENReading", enreading);
            sqlCommand.Parameters.AddWithValue(
            "@ENListening", enlistening);
            sqlCommand.Parameters.AddWithValue(
            "@Description", description);

            sqlCommand.ExecuteNonQuery();
            save = true;
            CloseConnection();
        }
        catch (SqlException sql)
        {
            errorCode = sql.Number;
            errorMessage = sql.Message;
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;

        }
        return save;
    }
}
